/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;

import java.text.*;
import java.util.Vector;

import Beans.NgonNgu;

/**
 *
 * @author Venus
 */
public class KhuyenMai_Model {

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaKM) from khuyenmai";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }

    }

    public Vector LayDSKM(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + " Order by MaKM desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenKM(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + " "
                    + " Order by TenKM " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayBDKM(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + " "
                    + " Order by NgayBD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayKTKM(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + " "
                    + " Order by NgayKT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        return v;
    }

    public Vector SortTTKM(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + " "
                    + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSLKM(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaLKM), MaNN, TenLKM from loaikhuyenmai where MaNN = " + langid + " Order by MaLKM asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKhuyenMai lkm = new Beans.LoaiKhuyenMai();
                lkm.setMaLKM(rss.getInt(1));
                lkm.setMaNN(rss.getInt(2));
                lkm.setTenLKM(rss.getString(3));
                v.add(lkm);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSLKMTao(int langid, String malkm) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaLKM), MaNN, TenLKM from loaikhuyenmai where MaNN = " + langid + " and MaLKM != " + malkm + " Order by MaLKM asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.LoaiKhuyenMai lkm = new Beans.LoaiKhuyenMai();
                lkm.setMaLKM(rss.getInt(1));
                lkm.setMaNN(rss.getInt(2));
                lkm.setTenLKM(rss.getString(3));
                v.add(lkm);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSNT(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaNgay), MaNN, TenNgay from ngaythu where MaNN = " + langid + " Order by MaNgay asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NgayThu nt = new Beans.NgayThu();
                nt.setMaNgay(rss.getInt(1));
                nt.setMaNN(rss.getInt(2));
                nt.setTenNgay(rss.getString(3));
                v.add(nt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector LayDSNTSearch(int mangay, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaNgay), MaNN, TenNgay from ngaythu where MaNN = " + langid + " and MaNgay != " + mangay + "  Order by MaNgay asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NgayThu nt = new Beans.NgayThu();
                nt.setMaNgay(rss.getInt(1));
                nt.setMaNN(rss.getInt(2));
                nt.setTenNgay(rss.getString(3));
                v.add(nt);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean ThemKhuyenMai(int MaKM, String promotionname, String loaikm, String datestart, String enddate, String content) // Tạo khu vực
    {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into khuyenmai values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "( " + MaKM + ", '" + promotionname + "', " + loaikm + ", " + dsnn[i].getMaNN() + ", '" + datestart + "', '" + enddate + "', '" + content + "', 1 ),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean ThemKMTHD(int MaKM, String condition, String discount) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Insert into kmtheohoadon(MaKM, DieuKien, GiamGia) values(" + MaKM + ", " + condition + ", " + discount + ") ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean ThemKMTN(String MaKM, String mangaythu, String mamonan, String dismonan) // Tạo khu vực
    {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Insert into kmtheongay( MaKM, MaNgay, MaMA, GiamGia) values ( " + MaKM + ", " + mangaythu + ", " + mamonan + ", " + dismonan + " ) ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTenNgay(int MaNgay, int langid) {
        String maht = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenNgay from ngaythu where  MaNgay = " + MaNgay + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayTenLoaiKM(int MaLKM, int langid) {
        String maht = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenLKM from loaikhuyenmai where  MaLKM = " + MaLKM + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public Vector SearchListKM(String TENKM, String BATDAU, String KETTHUC, String TINHTRANG, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + "  ";
            String[] arr = {TENKM, BATDAU, KETTHUC, TINHTRANG};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(BATDAU)) {
                    query = query + " and NgayBD = '" + BATDAU + "' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(KETTHUC)) {
                    query = query + " and NgayKT = '" + KETTHUC + "' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TINHTRANG)) {
                    query = query + " and TinhTrang = " + TINHTRANG + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENKM)) {
                    query = query + " and TenKM like '%" + arr[i] + "%' ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenKM(String TENKM, String BATDAU, String KETTHUC, String TINHTRANG, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + "  ";
            String[] arr = {TENKM, BATDAU, KETTHUC, TINHTRANG};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(BATDAU)) {
                    query = query + " and NgayBD = " + BATDAU + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(KETTHUC)) {
                    query = query + " and NgayKT = " + KETTHUC + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TINHTRANG)) {
                    query = query + " and TinhTrang = " + TINHTRANG + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENKM)) {
                    query = query + " and TenKM like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TenKM " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayBDKM(String TENKM, String BATDAU, String KETTHUC, String TINHTRANG, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + "  ";
            String[] arr = {TENKM, BATDAU, KETTHUC, TINHTRANG};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(BATDAU)) {
                    query = query + " and NgayBD = " + BATDAU + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(KETTHUC)) {
                    query = query + " and NgayKT = " + KETTHUC + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TINHTRANG)) {
                    query = query + " and TinhTrang = " + TINHTRANG + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENKM)) {
                    query = query + " and TenKM like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by NgayBD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayKTKM(String TENKM, String BATDAU, String KETTHUC, String TINHTRANG, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + "  ";
            String[] arr = {TENKM, BATDAU, KETTHUC, TINHTRANG};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(BATDAU)) {
                    query = query + " and NgayBD = " + BATDAU + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(KETTHUC)) {
                    query = query + " and NgayKT = " + KETTHUC + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TINHTRANG)) {
                    query = query + " and TinhTrang = " + TINHTRANG + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENKM)) {
                    query = query + " and TenKM like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by NgayKT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTKM(String TENKM, String BATDAU, String KETTHUC, String TINHTRANG, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKM), TenKM, MaLKM, MaNN, NgayBD, NgayKT, NoiDungKM, TinhTrang from khuyenmai where MaNN = " + langid + "  ";
            String[] arr = {TENKM, BATDAU, KETTHUC, TINHTRANG};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(BATDAU)) {
                    query = query + " and NgayBD = " + BATDAU + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(KETTHUC)) {
                    query = query + " and NgayKT = " + KETTHUC + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TINHTRANG)) {
                    query = query + " and TinhTrang = " + TINHTRANG + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENKM)) {
                    query = query + " and TenKM like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayKM(int makm) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khuyenmai where MaKM = " + makm;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayKMEdit(int makm, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from khuyenmai where MaKM = " + makm + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KhuyenMai km = new Beans.KhuyenMai();
                km.setMaKM(rss.getInt(1));
                km.setTenKM(rss.getString(2));
                km.setMaLKM(rss.getInt(3));
                km.setMaNN(rss.getInt(4));
                km.setNgayBD(rss.getString(5));
                km.setNgayKT(rss.getString(6));
                km.setNoiDungKM(rss.getString(7));
                km.setTinhTrang(rss.getByte(8));
                v.add(km);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean Edit(String makm, String mann, String TenKM, String NoiDung) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khuyenmai set TenKM = '" + TenKM + "', NoiDungKM = '" + NoiDung + "'  where MaKM = " + makm + " and MaNN = " + mann;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateStatusKM(String makm, String status) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khuyenmai set TinhTrang = " + status + " where MaKM = " + makm;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int CheckLoaiKM(String makm, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaLKM from khuyenmai where MaKM = " + makm + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LayGiamGia(String makm, int mama, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select GiamGia from kmtheongay where MaKM = " + makm + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayDSKMTN(int makm) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaKMTN), MaKM, MaNgay, MaMA, GiamGia from kmtheongay where MaKM = " + makm + " Order by MaKMTN desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean CheckNgayThu(int MaKM, int MaNgay, int MaMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from kmtheongay where MaKM = " + MaKM + " and MaNgay = " + MaNgay + " and MaMA = " + MaMA + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckNgayThuCaTuan(int MaKM, int MaMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from kmtheongay where MaKM = " + MaKM + " and MaMA = " + MaMA + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean DeleteProDay(int makmtn) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Delete from kmtheongay where MaKMTN = " + makmtn + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SortTenMAKM(int langid, String order, String makm) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, MaNgay, A.MaMA, GiamGia from kmtheongay A, monan B "
                    + " where MaKM = " + makm + " and A.MaMA = B.MaMA Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayKM(int langid, String order, String makm) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, A.MaNgay, A.MaMA, GiamGia from kmtheongay A, ngaythu B "
                    + " where MaKM = " + makm + " and A.MaNgay = B.MaNgay Order by TenNgay " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortGiaKM(int langid, String order, String makm) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, MaNgay, A.MaMA, GiamGia from kmtheongay A, gia B "
                    + " where MaKM = " + makm + " and A.MaMA = B.MaMA Order by Gia " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchAddPro(String tenma, String ngaythu, String gia, String giamgia, String makm, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, a.MaNgay, A.MaMA, GiamGia "
                    + " from kmtheongay A inner join monan B on A.MaMA = B.MaMA "
                    + " inner join ngaythu C on A.MaNgay = C.MaNgay "
                    + " left join gia D on B.MaMA = D.MaMA "
                    + " where MaKM = " + makm + " ";
            String[] arr = {tenma, ngaythu, gia, giamgia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenma)) {
                    query = query + " and TenMA like '%" + tenma + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(ngaythu)) {
                    query = query + " and TenNgay like '%" + ngaythu + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and Gia = " + gia + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(giamgia)) {
                    query = query + " and GiamGia = " + arr[i] + " ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenMAKM(String tenma, String ngaythu, String gia, String giamgia, String makm, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, a.MaNgay, A.MaMA, GiamGia "
                    + " from kmtheongay A inner join monan B on A.MaMA = B.MaMA "
                    + " inner join ngaythu C on A.MaNgay = C.MaNgay "
                    + " left join gia D on B.MaMA = D.MaMA "
                    + " where MaKM = " + makm + " ";
            String[] arr = {tenma, ngaythu, gia, giamgia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenma)) {
                    query = query + " and TenMA like '%" + tenma + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(ngaythu)) {
                    query = query + " and TenNgay like '%" + ngaythu + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and Gia = " + gia + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(giamgia)) {
                    query = query + " and GiamGia = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayKM(String tenma, String ngaythu, String gia, String giamgia, String makm, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, a.MaNgay, A.MaMA, GiamGia "
                    + " from kmtheongay A inner join monan B on A.MaMA = B.MaMA "
                    + " inner join ngaythu C on A.MaNgay = C.MaNgay "
                    + " left join gia D on B.MaMA = D.MaMA "
                    + " where MaKM = " + makm + " ";
            String[] arr = {tenma, ngaythu, gia, giamgia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenma)) {
                    query = query + " and TenMA like '%" + tenma + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(ngaythu)) {
                    query = query + " and TenNgay like '%" + ngaythu + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and Gia = " + gia + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(giamgia)) {
                    query = query + " and GiamGia = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenNgay " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchGiaKM(String tenma, String ngaythu, String gia, String giamgia, String makm, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaKMTN), MaKM, a.MaNgay, A.MaMA, GiamGia "
                    + " from kmtheongay A inner join monan B on A.MaMA = B.MaMA "
                    + " inner join ngaythu C on A.MaNgay = C.MaNgay "
                    + " left join gia D on B.MaMA = D.MaMA "
                    + " where MaKM = " + makm + " ";
            String[] arr = {tenma, ngaythu, gia, giamgia};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(tenma)) {
                    query = query + " and TenMA like '%" + tenma + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(ngaythu)) {
                    query = query + " and TenNgay like '%" + ngaythu + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(gia)) {
                    query = query + " and Gia = " + gia + " ";
                }
                if (!arr[i].equals("") && arr[i].equals(giamgia)) {
                    query = query + " and GiamGia = " + arr[i] + " ";
                }
            }
            query = query + " Order by Gia " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.KMTheoNgay kmtn = new Beans.KMTheoNgay();
                kmtn.setMaKMTN(rss.getInt(1));
                kmtn.setMaKM(rss.getInt(2));
                kmtn.setMaNgay(rss.getInt(3));
                kmtn.setMaMA(rss.getInt(4));
                kmtn.setGiamGia(rss.getInt(5));
                v.add(kmtn);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean UpdateGiamGia(int makmtn, int giamgia) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Update kmtheongay set GiamGia = " + giamgia + " where MaKMTN = " + makmtn + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static double LayDieuKien(int MaKM) {
        double maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select DieuKien from kmtheohoadon where  MaKM = " + MaKM + "  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getDouble(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static int LayGiamGiaHD(int MaKM) {
        int maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select GiamGia from kmtheohoadon where  MaKM = " + MaKM + "  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean updatepromotionbill(int makm, String datestart, String enddate, String promotionname, String content) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update khuyenmai set NgayBD = '" + datestart + "', NgayKT = '" + enddate + "', TenKM = '" + promotionname + "', NoiDungKM = '" + content + "' where MaKM = " + makm;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean updatedetailpromotionbill(int makm, String condition, String discount) {
        Connection conn = null;
        
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update kmtheohoadon set DieuKien = " + condition + ", GiamGia = " + discount + " where MaKM = " + makm;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDate(String tungay, String denngay) {
        try{
            int n = 0;
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            Calendar c1 = Calendar.getInstance();
            Calendar c2 = Calendar.getInstance();
            Date date1 = fm.parse(tungay);
            Date date2 = fm.parse(denngay);
            c1.setTime(date1);
            c2.setTime(date2);
            if (c1.after(c2)) {
                n = n + 1;
            }
            if (n == 0) {
                return true;
            }
            return false;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
    public static boolean CheckDateKM(String tungay, String denngay, String loaikm) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;        
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");            
            Calendar cfrom = Calendar.getInstance();
            Calendar cto = Calendar.getInstance();
            Calendar cbd = Calendar.getInstance();
            Calendar ckt = Calendar.getInstance();
            Date date1 = fm.parse(tungay);
            Date date2 = fm.parse(denngay);
            cfrom.setTime(date1);
            cto.setTime(date2);
            String query = " Select NgayBD, NgayKT from khuyenmai where MaLKM = "+loaikm+" and TinhTrang = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                String y = rss.getString(1);
                String x = rss.getString(2);
                Date date3 = fm.parse(y);
                Date date4 = fm.parse(x);
                cbd.setTime(date3);
                ckt.setTime(date4);
                if(cfrom.before(ckt) && cfrom.after(cbd) ){
                    n = n + 1;
                }
                if(cfrom.equals(ckt) || cfrom.equals(cbd) || cto.equals(ckt) || cto.equals(cbd) ){
                    //n = n + 1;
                }
                if(cto.before(ckt) && cto.after(cbd) ){
                    //n = n + 1;
                }
            }
            stmt.close();
            conn.close();
            if (n == 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        
    }
    public static boolean CheckDateKMUpdate(String tungay, String denngay, String loaikm, String makm) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            Calendar cfrom = Calendar.getInstance();
            Calendar cto = Calendar.getInstance();
            Calendar cbd = Calendar.getInstance();
            Calendar ckt = Calendar.getInstance();
            Date date1 = fm.parse(tungay);
            Date date2 = fm.parse(denngay);
            cfrom.setTime(date1);
            cto.setTime(date2);
            String query = " Select NgayBD, NgayKT from khuyenmai where MaLKM = "+loaikm+" and MaKM != "+makm+" and TinhTrang = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                String y = rss.getString(1);
                String x = rss.getString(2);
                Date date3 = fm.parse(y);
                Date date4 = fm.parse(x);
                cbd.setTime(date3);
                ckt.setTime(date4);
                if(cfrom.before(ckt) && cfrom.after(cbd) ){
                    n = n + 1;
                }
                if(cfrom.equals(ckt) || cfrom.equals(cbd) || cto.equals(ckt) || cto.equals(cbd) ){
                    n = n + 1;
                }
                if(cto.before(ckt) && cto.after(cbd) ){
                    n = n + 1;
                }
            }
            stmt.close();
            conn.close();
            if (n == 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean DeleteKM(String makm) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;        
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from khuyenmai where MaKM = " + makm ;
            String query1 = " Delete from kmtheohoadon where MaKM = " + makm ;
            String query2 = " Delete from kmtheongay where MaKM = " + makm ;
            int n = stmt.executeUpdate(query);
            stmt.executeUpdate(query1);
            stmt.executeUpdate(query2);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }        
    }
}
